' The SqlClient namespace is only needed if you use obj.Query.ReturnReader
Imports System.Data.SqlClient

' By using the Import statement your code will be more readable when using enums and such
Imports MyGeneration.dOOdads

' Employees was generated from a Table using "VbNet_SQL_dOOdads_BusinessEntity.vbgen"
' Products was generated from a View using "VbNet_SQL_dOOdads_View.vbgen"


Public Class TheMasterSample

    ' Some methods expect a stored proc to exist in your database, you can build the required 
    ' stored procs with 'SQL_StoredProcs.vbgen'

    Public Sub SimpleLoad()

        ' LoadAll() expects a stored proc to exist in your database
        Dim emps As New Employees
        If emps.LoadAll() Then   ' [proc_EmployeesLoadAll]
            ' At least one row was loaded
        End If

        ' LoadAll is the same as (but maybe less efficient) than this
        emps = New Employees
        If emps.Query.Load() Then ' SELECT * FROM Employees 
            ' At least one row was loaded
        End If

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' LoadAll() and Query.Load() with no Where clause yield the same results
        ' however, Query.Load() builds dynamic SQL, all query data is passed
        ' in via SqlParameters so there is no chance for hackers to attempt
        ' "sql injection" techniques
        '-----------------------------------------------------------

    End Sub

    Public Sub MoreComplexLoad()

        Dim emps As New Employees

        ' LastNames that have "A" anywhere in them
        emps.Where.LastName.Value = "%A%"
        emps.Where.LastName.[Operator] = WhereParameter.Operand.Like_

        emps.Query.Load()

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' You can Query just about anyway you need to with the Where
        ' clause, don't bother writing tons of specific stored procedures
        ' that you'll later have to maintain
        '-----------------------------------------------------------

    End Sub

    Public Sub TheDeluxeQuery()

        Dim emps As New Employees

        ' LastNames that have "A" anywher in them
        emps.Where.LastName.Value = "%A%"
        emps.Where.LastName.[Operator] = WhereParameter.Operand.Like_

        ' Only return the EmployeeID and LastName
        emps.Query.AddResultColumn(Employees.ColumnNames.EmployeeID)
        emps.Query.AddResultColumn(Employees.ColumnNames.LastName)

        ' Order by LastName 
        ' (you can add as many order by columns as you like by repeatedly calling this)
        emps.Query.AddOrderBy(Employees.ColumnNames.LastName, WhereParameter.Dir.ASC)

        ' Bring back only distinct rows
        emps.Query.Distinct = True

        ' Bring back the top 10 rows
        emps.Query.Top = 10

        emps.Query.Load()

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' There are many morals here, again, the power of the dynamic
        ' query shines through, you'll create screens so quick even you
        ' will be surprized.
        ' 
        ' 1) Never use strings like "FirstName" for anything, use your
        '    business entities ColumnsNames data, such as "Employees.ColumnNames.EmployeeID"
        '    This way if you drop the column from the table and regenerate you'll 
        '    get a compile error anywhere it was used. Let the compiler do the 
        '    work for you
        '
        '  2) You can reduce the number of columns returned in your result set
        '     though "ResultColumnAdd" but just remember that if you access
        '     poperties for columns that you didn't return an exception will
        '     be thrown. 
        '
        '  3) Add as many order by columns that you need by repeatedly calling "AddOrderBy"
        '-----------------------------------------------------------

    End Sub

    Public Sub GenerateSql()

        Dim emps As New Employees
        Dim query As String = emps.Query.GenerateSQL()

        'NOTE: It's better to use 'emps.Query.LastQuery'

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' Below is the query text generated by the query in TheDeluxeQuery
        ' GenerateSQL was created as a unit testing device and was left in
        ' for debugging purposes. After calling this you cannot load the object.
        ' If you want to see the sql after the call use 'emps.Query.LastQuery'
        ' 
        ' "SELECT DISTINCT TOP 10 EmployeeID,LastName FROM [Employees] WHERE [LastName] " 
        ' "LIKE @LastName ORDER BY [LastName] ASC"
        '-----------------------------------------------------------

    End Sub

    Public Sub DataReader()

        Dim emps As New Employees

        ' LastNames that have "A" anywhere in them
        emps.Where.LastName.Value = "%A%"
        emps.Where.LastName.[Operator] = WhereParameter.Operand.Like_

        Dim reader As SqlDataReader = CType(emps.Query.ReturnReader, SqlDataReader)

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' This can be useful for Quick binding, however, you can always
        ' bind to emps.DefaultView. 
        '
        ' ReturnReader doesn't actually populate your buisness entity
        ' it merely returns data in the SqlDataReader
        '-----------------------------------------------------------

    End Sub

    Public Sub Iteration()

        Dim emps As New Employees
        If emps.LoadAll() Then

            Dim lastName As String

            ' Iteration walks the DataTable.DefaultView, see the FilterAndSort
            ' sample for further clarification.
            Do
                lastName = emps.LastName
            Loop Until Not emps.MoveNext

            emps.Rewind()

            Do
                lastName = emps.LastName
            Loop Until Not emps.MoveNext

        End If

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' Iteration is simple, you can rewind and restart at any time
        '-----------------------------------------------------------

    End Sub

    Public Sub FilterAndSort()

        Dim emps As New Employees
        If emps.LoadAll Then

            ' After you load your business entity you can apply Sort and Filter,
            ' you could also potentially do this in the Where clause too
            emps.Filter = Employees.ColumnNames.City + " = 'Berlin'"
            emps.Sort = Employees.ColumnNames.LastName + " DESC"

            ' Filter might have "hidden" all of the rows
            If emps.RowCount > 0 Then

                Dim lastName As String

                ' Remember, iteration walks the DataTable.DefaultView which is 
                ' effected by Sort and Filter, so you'll only get Employees who
                ' live in Berlin, and they'll fed to your MoveNext loop in decending
                ' order by LastName
                Do
                    lastName = emps.LastName
                Loop Until Not emps.MoveNext

                '-----------------------------------------------------------
                ' Moral: 
                '-----------------------------------------------------------
                ' Sort and Filter can be great ways to implement data grid
                ' filtering in your user interface when you don't want to requery 
                ' the database, just bind your grid to emps.DefaultView
                '-----------------------------------------------------------
            End If
        End If

    End Sub

    Public Sub DemonstrateBulkUpdates()

        Dim emps As New Employees
        If emps.LoadAll() Then

            ' Modify the LastName column in every row
            Do
                emps.LastName = emps.LastName + "W"
            Loop Until Not emps.MoveNext

            ' Rewind and mark the first row as Deleted
            'emps.Rewind()
            'emps.MarkAsDeleted()

            ' Add a new row and fill it in
            emps.AddNew()
            emps.FirstName = "Jimmy"
            emps.LastName = "Lunch Box"

            ' Save all modifications, deletes, and new rows 
            emps.Save()

        End If

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' This is a very nice way to work. When you generate your
        ' stored procedures using 'SQL_StoredProcs.vbgen' you'll find
        ' it is pretty smart. It makes any identity columns
        ' and or computed columns as OUTPUT parameters. Thus, after Save()
        ' any new rows or updated rows have their identity
        ' columns or calulated columns already in them, no
        ' requerying the database
        ' 
        ' You never have to use a transaction when saving a single object.
        ' The dOOdad architecture always does this for you.         
        '-----------------------------------------------------------

    End Sub

    Public Sub Transactions()

        Dim tx As TransactionMgr
        tx = TransactionMgr.ThreadTransactionMgr()

        Try

            Dim emps As New Employees
            emps.AddNew()
            emps.FirstName = "Jimmy"
            emps.LastName = "Lunch Box"

            Dim prds As New Products
            prds.AddNew()
            prds.ProductName = "dOOdads"
            prds.Discontinued = False

            tx.BeginTransaction()
            emps.Save()
            prds.Save()
            tx.CommitTransaction()

        Catch ex As Exception

            tx.RollbackTransaction()
			TransactionMgr.ThreadTransactionMgrReset()

        End Try

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' Modeled after COM+ transactions, but still using ADO.NET
        ' connection based transactions you have the best of both
        ' worlds.
        '
        ' 1) Your transactions paths do not have to be pre-planned.
        '    At any time you can begin a transaction
        ' 
        ' 2) You can nest BeginTransaction/CommitTransaction any number of times as
        '    long as they are sandwiched appropriately
        '
        '    BeginTransaction
        '        BeginTransaction
        '            emps.Save
        '        CommitTransaction                                        
        '    CommitTransaction
        '
        '    Only the final CommitTransaction will commit the transaction
        '                        
        ' 3) Once RollbackTransaction is called the transaction is doomed,
        '    nothing can be committed even it is attempted.
        '
        ' 4) Transactions are stored in the Thread Local Storage or
        '    TLS. This way the API isn't intrusive, ie, forcing you
        '    to pass a SqlConnection around everywhere.  There is one
        '    thing to remember, once you call RollbackTransaction you will
        '    be unable to commit anything on that thread until you
        '    call ThreadTransactionMgrReset().
        ' 
        '    In an ASP.NET application each page is handled by a thread
        '    that is pulled from a thread pool. Thus, you need to clear
        '    out the TLS (thread local storage) before your page begins
        '    execution. The best way to do this is to create a base page
        '    that inhertis from System.Web.UI.Page and clears the state
        '    like this
        '
        '    Public Class MyPage
        '       Inherits System.Web.UI.Page
        ' 
        '       Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        '           TransactionMgr.ThreadTransactionMgrReset()
        '       End Sub
        '
        '    End Class
        '
        '    And then make sure all of your ASPX pages inherit from MyPage.
        '                        
        '-----------------------------------------------------------
    End Sub

    Public Sub FillComboBox()

        Dim prds As New Products

        ' Note we only bring back these two columns for performance reasons, why bring back more?
		prds.Query.AddResultColumn(Products.ColumnNames.ProductID)
		prds.Query.AddResultColumn(Products.ColumnNames.ProductName)

        ' Sort
		prds.Query.AddOrderBy(Products.ColumnNames.ProductName, MyGeneration.dOOdads.WhereParameter.Dir.ASC)

        ' Load it
        prds.Query.Load()

        ' Bind it (there no combo box in this code, see demo)
        ' Me.cmbBox.DisplayMember = prds.ColumnNames.ProductName
        ' Me.cmbBox.DataSource    = prds.DefaultView

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' You will find that a dOOdads can do almost anything, no need to write a million little 
        ' specific stored procedures, this code limits the columns, sorts, and fills a combobox
        ' there's nothing to it
        '-----------------------------------------------------------

    End Sub

    Public Sub AddColumn()

        Dim emps As New Employees
        If emps.LoadAll() Then

            Dim col As DataColumn = emps.AddColumn("FullName", Type.GetType("System.String"))
            col.Expression = Employees.ColumnNames.LastName + "+ ', ' + " + Employees.ColumnNames.FirstName

            Dim fullName As String

            Do
                fullName = CType(emps.GetColumn("FullName"), String)
            Loop Until Not emps.MoveNext

        End If

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' Of course if you add a column Dynamically as the code does above
        ' you'll have no strongly typed accessor like emps.FullName, but you
        ' can use GetColumn() to access dynamic columns.
        '
        ' Never use this to access other fields in your business entity
        ' although it may work, it's poor programming and always use
        ' your ColumnNames property and not hardcoded strings when possible
        '-----------------------------------------------------------

    End Sub

    Public Sub Serialize()

        Dim emps As New Employees
        emps.Query.Load()              ' emps.RowCount = 200
        emps.FirstName = "Griffinski"  ' Change first row
        emps.GetChanges()              ' emps.RowCount now = 1 
        Dim xml As String = emps.ToXml()

        ' Now reload that single record into a new Employees object and Save it
        Dim empsClone As New Employees
        empsClone.FromXml(xml)
        empsClone.Save()

        '-----------------------------------------------------------
        ' Moral: 
        '-----------------------------------------------------------
        ' This really only serializes the data in the embedded DataTable.
        ' However, the methods used in the sample above our Overridable
        ' so you can override them.
        '-----------------------------------------------------------

	End Sub

	Public Sub AggregateTest()

		' AVG
		Dim prds As Products = New Products
		prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Avg
		prds.Aggregate.UnitsInStock.Alias = "Average Units in Stock"
		prds.Query.Load()

		Console.WriteLine(prds.Query.LastQuery)

		' COUNT - To include a COUNT(*) with NULLs included
		prds = New Products
		prds.Query.CountAll = True
		prds.Query.CountAllAlias = "Product Count"
		prds.Query.Load()

		Console.WriteLine(prds.Query.LastQuery)

		' COUNT - To exclude NULLs in the COUNT for a column
		prds = New Products
		prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count
		prds.Aggregate.UnitsInStock.Alias = "With Stock"
		prds.Query.Load()

		Console.WriteLine(prds.Query.LastQuery)

		' To have two aggregates for the same column, use a  Tearoff
		prds = New Products
		prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count
		prds.Aggregate.UnitsInStock.Alias = "With Stock"

		Dim ap As AggregateParameter = prds.Aggregate.TearOff.UnitsInStock
		ap.Function = AggregateParameter.Func.Sum
		ap.Alias = "Total Units"

		prds.Query.Load()

		Console.WriteLine(prds.Query.LastQuery)

		' AddGroupBy
		prds = New Products
		prds.Aggregate.UnitsInStock.Function = AggregateParameter.Func.Count
		prds.Aggregate.UnitsInStock.Alias = "In Stock"
		prds.Query.AddResultColumn(Products.ColumnNames.CategoryID)
		prds.Query.AddGroupBy(Products.ColumnNames.CategoryID)
		'	prds.Query.AddGroupBy(prds.Aggregate.UnitsInStock)

		prds.Query.Load()

		Console.WriteLine(prds.Query.LastQuery)
	End Sub


End Class